package com.artieksi.dao;

import com.artieksi.entity.SignBean;
import com.artieksi.entity.rowmapper.SignBeanRowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * User: TTACANSEVER
 */
@Repository
public class ArtiEksiDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<SignBean> getTodaysSigns(String sign){
        String sql = " select k.signed_username, k.picture_url, count(1) c from " +
                            " (select signed_username, picture_url from signs where sign_time > CURRENT_DATE - INTERVAL 1 DAY " +
                            "    and sign = ? group by signer_id, signed_id) k " +
                     " group by k.signed_username, k.picture_url order by c desc, k.signed_username limit 20";
        return jdbcTemplate.query(sql, new SignBeanRowMapper(sign), sign);
    }
    
    public List<SignBean> getThisWeeksSigns(String sign){
        String sql = " select k.signed_username, k.picture_url, count(1) c from " +
                            " (select signed_username, picture_url, sign_time from signs where sign_time > CURRENT_DATE - INTERVAL 7 DAY " +
                            "    and sign = ? group by signer_id, signed_id) k " +
                     " group by k.signed_username, k.picture_url order by c desc, k.signed_username limit 20 ";
        return jdbcTemplate.query(sql, new SignBeanRowMapper(sign), sign);
    }

    public List<SignBean> getThisMonthsSigns(String sign){
        String sql = " select k.signed_username, k.picture_url, count(1) c from " +
                " (select signed_username, picture_url, sign_time from signs where sign_time > CURRENT_DATE - INTERVAL 30 DAY " +
                "    and sign = ? group by signer_id, signed_id) k " +
                " group by k.signed_username, k.picture_url order by c desc, k.signed_username limit 20 ";
        return jdbcTemplate.query(sql, new SignBeanRowMapper(sign), sign);
    }

    public SignBean searchUser(String username, String sign, String interval){
        String sql = null;
        if("daily".equals(interval)) {
            sql = " select k.signed_username, k.picture_url, count(1) c from " +
                    " (select signed_username, picture_url from signs where DATE(sign_time) = DATE(NOW()) " +
                    "    and signed_username = ? and sign = ? group by signer_id, signed_id) k " +
                    " group by k.signed_username";
        } else if("weekly".equals(interval)) {
            sql = " select k.signed_username, k.picture_url, count(1) c from " +
                    " (select signed_username, picture_url from signs where sign_time > CURRENT_DATE - INTERVAL 7 DAY  " +
                    "    and signed_username = ? and sign = ? group by signer_id, signed_id) k " +
                    " group by k.signed_username";
        } else if("monthly".equals(interval)) {
            sql = " select k.signed_username, k.picture_url, count(1) c from " +
                    " (select signed_username, picture_url from signs where sign_time > CURRENT_DATE - INTERVAL 30 DAY " +
                    "    and signed_username = ? and sign = ? group by signer_id, signed_id) k " +
                    " group by k.signed_username";
        };
        return jdbcTemplate.queryForObject(sql, new SignBeanRowMapper(sign), username, sign);
    }

    public List<SignBean> getUserTweets(String signedUsername, String sign){
        String sql = " select * from signs where signed_username = ? and sign = ? and sign_time > CURRENT_DATE - INTERVAL 30 DAY " +
                " order by sign_time desc limit 100 ";
        return jdbcTemplate.query(sql, new SignBeanRowMapper(sign), signedUsername, sign);
    }


}
